package com.wuseguang.springcloud.service.impl;

import com.wuseguang.springcloud.dao.jdbc.AreaPlatformJdbc;
import com.wuseguang.springcloud.dao.repository.AreaPlatformRepository;
import com.wuseguang.springcloud.entity.AreaPlatform;
import com.wuseguang.springcloud.service.AreaPlatformService;
import com.wuseguang.springcloud.util.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.text.NumberFormat;
import java.util.*;

/**
 * <p>
 * 区级平台账号 服务实现类
 * </p>
 *
 * @author
 * @since 2020-08-12
 */
@Service
public class AreaPlatformServiceImpl implements AreaPlatformService {

    private Logger logger = LoggerFactory.getLogger(this.getClass());
    @Autowired
    private AreaPlatformRepository areaPlatformRepository;
    @Autowired
    private AreaPlatformJdbc areaPlatformJdbc;

    @Override
    public Result addAreaPlatform(AreaPlatform areaPlatform) {

        logger.info("添加区级平台服务实现类");
        areaPlatform.setPassword(MyDES.encryptBasedDes(areaPlatform.getPassword()));
        areaPlatform.setAddTime(new Date());
        areaPlatformRepository.save(areaPlatform);
        return new Result();
    }

    @Override
    public Result updateAreaPlatform(AreaPlatform areaPlatform) {
        logger.info("修改区级平台服务实现类");
        //创建修改工具
        Optional<AreaPlatform> optional = areaPlatformRepository.findById(areaPlatform.getId());
        UpdateTool.copyNullProperties(optional.get(), areaPlatform);
        areaPlatform.setPassword(MyDES.encryptBasedDes(areaPlatform.getPassword()));
        areaPlatformRepository.save(areaPlatform);
        return new Result();
    }

    @Override
    public Result delteAreaPlatform(Integer id) {
        logger.info("删除区级平台服务实现类");
        areaPlatformRepository.deleteById(id);
        return new Result();
    }

    @Override
    public Result findAreaPopulation(Integer areaPlatformId) {

        logger.info("查询区级的总体情况");
        //查询区级下面的学校总数
        String sql = "select count(1) `count`  from school where area_platform_id = ?";
        Integer schoolCount = areaPlatformJdbc.queryCount(sql, areaPlatformId);

        //查询家长数量（学生一对一家长）
        sql = "select count(1) `count` from student s where EXISTS" +
                "(select * from school c where c.id=s.school_id and c.area_platform_id = ? )";
        Integer parentCount = areaPlatformJdbc.queryCount(sql, areaPlatformId);

        //查询学校成员（学校成员等于教师+学生）
        sql = "select count(1) + ? `count` from teacher t where " +
                "EXISTS(select * from school c where c.id=t.school_id and c.area_platform_id = ? )";
        Integer teacherCount = areaPlatformJdbc.queryCount(sql, parentCount, areaPlatformId);

       /* //已加入智慧校园的数量
        sql = "select if(sum(`count`) is null,0,sum(`count`)) `count` from ( " +
                "select count(1) `count` from student s left join  area_login_times alt on(s.id=alt.user_id) where " +
                " EXISTS (select * from school c where c.id=s.school_id and c.area_platform_id = ? ) and  alt.type=3 GROUP BY s.id " +
                " UNION  all " +
                "select count(1) `count` from teacher t left join  area_login_times alt on(t.id=alt.user_id) where " +
                " EXISTS (select * from school c where c.id=t.school_id and c.area_platform_id = ? ) and  alt.type=1 GROUP BY t.id " +
                " UNION  all " +
                "select count(1) `count` from student s left join  area_login_times alt on(s.id=alt.user_id) where " +
                "EXISTS (select * from school c where c.id=s.school_id and c.area_platform_id = ? ) and  alt.type=2 GROUP BY s.id " +
                ") a ";
        Integer joinCount = areaPlatformJdbc.queryCount(sql,areaPlatformId,areaPlatformId,areaPlatformId);*/


        Map<String, Object> map = new HashMap<>();
        map.put("parentCount", parentCount);
        map.put("teacherCount", teacherCount);
        map.put("schoolCount", schoolCount);
        return new Result(map);
    }

    @Override
    public Result findAreaUsageRecord(Integer areaPlatformId) {

        logger.info("查询区级的使用记录");


        //总使用的人数（最起码登录过一次的人）
        String sql = "select if(sum(`count`) is null,0,sum(`count`)) `count` from ( " +
                "select count(1) `count` from student s left join  area_login_times alt on(s.id=alt.user_id) where " +
                " EXISTS (select * from school c where c.id=s.school_id and c.area_platform_id = ? ) and  alt.type=3 GROUP BY s.id " +
                " UNION  all " +
                "select count(1) `count` from teacher t left join  area_login_times alt on(t.id=alt.user_id) where " +
                " EXISTS (select * from school c where c.id=t.school_id and c.area_platform_id = ? ) and  alt.type=1 GROUP BY t.id " +
                " UNION  all " +
                "select count(1) `count` from student s left join  area_login_times alt on(s.id=alt.user_id) where " +
                "EXISTS (select * from school c where c.id=s.school_id and c.area_platform_id = ? ) and  alt.type=2 GROUP BY s.id " +
                ") a ";
        Integer totalUsers = areaPlatformJdbc.queryCount(sql, areaPlatformId, areaPlatformId, areaPlatformId);

        //总是用次数
        sql = "select if(sum(`count`) is null,0,sum(`count`)) `count` from ( " +
                "select count(1) `count` from student s left join  area_login_times alt on(s.id=alt.user_id) where " +
                " EXISTS (select * from school c where c.id=s.school_id and c.area_platform_id = ? ) and  alt.type=3  " +
                " UNION  all " +
                "select count(1) `count` from teacher t left join  area_login_times alt on(t.id=alt.user_id) where " +
                " EXISTS (select * from school c where c.id=t.school_id and c.area_platform_id = ? ) and  alt.type=1  " +
                " UNION  all " +
                "select count(1) `count` from student s left join  area_login_times alt on(s.id=alt.user_id) where " +
                "EXISTS (select * from school c where c.id=s.school_id and c.area_platform_id = ? ) and  alt.type=2 " +
                ") a ";
        Integer totalUsageTimes = areaPlatformJdbc.queryCount(sql, areaPlatformId, areaPlatformId, areaPlatformId);


        //当天活跃人数
        sql = "select  sum(`count`) `count` from ( " +
                " select count(1) count from area_active aa left join student s  on(aa.user_id=s.id) where " +
                " exists(select * from school c where c.id=s.school_id and c.area_platform_id = ?) and type = 3 " +
                " and TO_DAYS(add_time) = TO_DAYS(NOW()) and active = 1 " +
                " UNION  all " +
                " select count(1) count from area_active aa left join student s  on(aa.user_id=s.id) where " +
                " exists(select * from school c where c.id=s.school_id and c.area_platform_id = ?) and type = 2  " +
                " and TO_DAYS(add_time) = TO_DAYS(NOW()) and active = 1 " +
                " UNION  all " +
                " select count(1) count from area_active aa left join teacher t  on(aa.user_id=t.id) where " +
                " exists(select * from school c where c.id=t.school_id and c.area_platform_id = ?) and type = 1  " +
                " and TO_DAYS(add_time) = TO_DAYS(NOW()) and active = 1 " +
                ")a";
        Integer active = areaPlatformJdbc.queryCount(sql, areaPlatformId, areaPlatformId, areaPlatformId);

        // 创建一个数值格bai式化对象  
        NumberFormat numberFormat = NumberFormat.getInstance();
        // 设置精du确到小数点后1位值
        numberFormat.setMaximumFractionDigits(1);
        //活页百分比=当天活跃人数/使用总人数*100
        String result = numberFormat.format((float) active / (float) totalUsers * 100);

        Map<String, Object> map = new HashMap<>();
        //使用总人数
        map.put("totalUsers", totalUsers);
        //当天活跃人数
        map.put("totalUsageTimes", totalUsageTimes);
        //活跃百分比
        map.put("active", result);
        return new Result(map);
    }

    @Override
    public Result findAreaBinding(Integer areaPlatformId) {

        logger.info("查询区级绑定数");

        //查询家长数量（学生一对一家长）
        String sql = "select count(1) `count` from student s where EXISTS" +
                "(select * from school c where c.id=s.school_id and c.area_platform_id = ? )";
        Integer parentCount = areaPlatformJdbc.queryCount(sql, areaPlatformId);

        //查询教师数量
        sql = "select count(1) `count` from teacher t where " +
                "EXISTS(select * from school c where c.id=t.school_id and c.area_platform_id = ? )";
        Integer teacherCount = areaPlatformJdbc.queryCount(sql, areaPlatformId);

        //查询教师学生家长的是否登录（type=3学生，1教师，2家长）
        sql = "select  sum(`count`) count,type from ( " +
                " select count(alt.user_id)  `count` ,3 type from student s left join  area_login_times alt " +
                " on(s.id=alt.user_id and  alt.type=3) where EXISTS (select * from school c where c.id=s.school_id and c.area_platform_id = ? )  GROUP BY s.id " +
                " UNION  all " +
                " select count(alt.user_id) `count`  ,1 type from teacher t left join  area_login_times alt " +
                " on(t.id=alt.user_id and  alt.type=1) where EXISTS (select * from school c where c.id=t.school_id and c.area_platform_id = ? )  GROUP BY t.id " +
                " UNION  all " +
                " select count(alt.user_id) `count`  ,2 type from student s left join  area_login_times alt " +
                " on(s.id=alt.user_id and  alt.type=2) where EXISTS (select * from school c where c.id=s.school_id and c.area_platform_id = ? )  GROUP BY s.id " +
                ") a   GROUP BY type ORDER BY type asc";

        List<Map<String, Object>> list = areaPlatformJdbc.queryListMap(sql, areaPlatformId, areaPlatformId, areaPlatformId);


        //返回信息
        Map<String, Object> map = new HashMap<>();
        map.put("teacherCount", teacherCount);
        map.put("studentCount", parentCount);
        map.put("parentCount", parentCount);
        map.put("teacherBinding", list.get(0).get("count"));
        map.put("studentBindingt", list.get(2).get("count"));
        map.put("parentBinding", list.get(1).get("count"));


        return new Result(map);
    }

    @Override
    public Result findAreaSchool(Integer areaPlatformId) {
        logger.info("查询区级学校");
        String sql = "select id,school_name from school where area_platform_id = ?";
        List<Map<String, Object>> list = areaPlatformJdbc.queryListMap(sql, areaPlatformId);
        return new Result(list);
    }

    @Override
    public Result findAreaSchoolsBinding(Integer areaPlatformId) {
        logger.info("查询区级里面的各校绑定率");
        String sql = "select id,school_name from school where area_platform_id = ?";
        List<Map<String, Object>> list = areaPlatformJdbc.queryListMap(sql, areaPlatformId);

        //查询学校各个绑定过的数量（意思就是登录过的数量）
        sql = "select TRUNCATE(sum(studentBindingt) / ? * 100,1)  studentBindingt, TRUNCATE(sum(teacherBinding) / ? * 100,1) " +
                " teacherBinding,TRUNCATE(sum(parentBinding) / ? * 100,1) parentBinding,TRUNCATE((sum(studentBindingt)+sum(teacherBinding)+sum(parentBinding)) / ? * 100,1) populationBinding from( " +
                " select count(s.id)  `studentBindingt`,0 teacherBinding,0 parentBinding  from student s " +
                " left join  area_login_times alt on(s.id=alt.user_id and  alt.type=3)  where s.school_id= ? and alt.user_id is not null " +
                " UNION  " +
                " select 0 `studentBindingt`,count(t.id)`teacherBinding`,0 parentBinding  from teacher t " +
                " left join  area_login_times alt on(t.id=alt.user_id and  alt.type=1)  where t.school_id= ? and alt.user_id is not null " +
                " UNION  " +
                " select 0 `studentBindingt`,0 teacherBinding,count(s.id) `parentBinding` from student s " +
                " left join  area_login_times alt on(s.id=alt.user_id and  alt.type=2)  where s.school_id= ? and alt.user_id is not null " +
                ") a   ";
        //查询学校总教师数sql
        String teacherSql = "select count(1) `count` from teacher where school_id = ?";
        //查询学校总学生数sql
        String studentSql = "select count(1) `count` from student where school_id = ?";
        //循环查询每个学校的绑定率
        for (Map<String, Object> map : list) {
            //教师
            Integer teacherCount = areaPlatformJdbc.queryCount(teacherSql, map.get("id"));
            //学生
            Integer studentCount = areaPlatformJdbc.queryCount(studentSql, map.get("id"));
            //参数如下：学生总人数，教师总人数，家长总人数，前面三个之和，学校id*3
            Map<String, Object> schooolMap = areaPlatformJdbc.queryMap(sql, studentCount, teacherCount, studentCount, studentCount + teacherCount + studentCount, map.get("id"), map.get("id"), map.get("id"));
            map.put("schooolMap", schooolMap);
        }

        return new Result(list);
    }

    @Override
    public Result findArea30DaysActive(Integer areaPlatformId) {

        logger.info("查询区级里30天的各校活跃排名");
        //查询区里面的学校活跃排名
        String sql = "select * from (select school_name, " +
                " TRUNCATE(count(aa.id)/30/((select count(1) `count` from teacher t where school_id =  s.id)+(select count(1) `count` from student  where school_id =  s.id)*2)*100,2) count from  " +
                " school s left join   area_active aa on(s.id=aa.school_id and active = 1 and  DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(add_time))" +
                " where s.area_platform_id=?)  a ORDER BY count desc";

        List<Map<String, Object>> list = areaPlatformJdbc.queryListMap(sql, areaPlatformId);
        return new Result(list);
    }

    @Override
    public Result findArea30DaysUseData(Integer valueOf, Integer schoolId) {
        logger.info("查询区级某个学校30天的使用数据");
        //查询学校的30天内评价活跃度
        String sql = "select  TRUNCATE(count(aa.id)/30/((select count(1) `count` from teacher t where school_id =  s.id)+(select count(1) `count` from student  where school_id =  s.id)*2)*100,2) `doubles` from  " +
                " school s left join   area_active aa on(s.id=aa.school_id and active = 1 and  DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(add_time))" +
                " where s.id=?";
        double count1 = areaPlatformJdbc.queryBouble(sql, schoolId);
        //查询学校30天内的使用次数
        sql = "select if(count(1) is null,0,count(1)) count from area_login_times where school_id = ? " +
                "and  DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(login_time) ";
        Integer count2 = areaPlatformJdbc.queryCount(sql, schoolId);
        //查询30天内的使用人数
        sql = "select  count(1) count from ( " + sql + " GROUP BY type,user_id ) a";
        Integer count3 = areaPlatformJdbc.queryCount(sql, schoolId);

        //返回数据
        Map<String, Object> map = new HashMap<>();
        map.put("active", count1);
        map.put("usageTimes", count2);
        map.put("numberOfUsers", count3);
        return new Result(map);
    }


    @Override
    public Result findAreaPlatform(String adminId, String role, Integer indexNo, Integer rows) {
        logger.info("查询区级平台");
        List<Map<String, Object>> list = new ArrayList<>();
        //如果等于运营商
        if ("3".equals(role)) {
            String sql = "select id,phone,password,area_name,jian_cheng from area_platform where school_admin_id = ?";
            list = areaPlatformJdbc.queryListMap(sql, adminId);
        } else {
            String sql = "select id,phone,password,area_name,jian_cheng from area_platform ";
            list = areaPlatformJdbc.queryListMap(sql);
        }
        //创建分页工具
        PagingTool tool = new PagingTool();
        //放入页码
        tool.setIndexNo(indexNo);
        //放入每页显示数量
        tool.setRows(rows);
        //放入角色集合
        tool.setList(list);

        List<Map<String, Object>> listTwo = (List<Map<String, Object>>) tool.getList();
        //遍历修改某个字段
        list.iterator().forEachRemaining(str -> {
            str.put("password", MyDES.decryptBasedDes(str.get("password").toString()));
        });
        //返回数据
        return new Result(new PaginationUtil<>(list.size(), rows, listTwo, indexNo));
    }

    @Override
    public Result findCumulativeData(Integer areaPlatformId) {
        logger.info("查询区级累计数据总量-通知，图片，视频，文件，链接，评价，考勤");

        //查询文件总量
        String sql = "SELECT GROUP_CONCAT( annex ) string FROM notice n WHERE n.annex  IS NOT NULL AND n.annex != ''" +
                " and EXISTS (select * from school s where n.school_id=s.id and area_platform_id = ?  )";
        String fileTotal = areaPlatformJdbc.queryString(sql, areaPlatformId);

        //查询通知总量
        sql = "SELECT COUNT(1) count FROM notice n WHERE " +
                "EXISTS (select * from school s where n.school_id=s.id and area_platform_id = ?  )";
        Integer noticeTotal = areaPlatformJdbc.queryCount(sql, areaPlatformId);

        //查询链接总量
        sql = "SELECT COUNT(1) count FROM notice n WHERE n.website  IS NOT NULL AND n.website != '' and " +
                "EXISTS (select * from school s where n.school_id=s.id and area_platform_id = ?  )";
        Integer websiteTotal = areaPlatformJdbc.queryCount(sql, areaPlatformId);

        //查询图片总量
        sql = "SELECT COUNT(1) count FROM pictures p WHERE  " +
                "EXISTS (select * from school s where p.school_id=s.id and area_platform_id = ? )";
        Integer picturesTotal = areaPlatformJdbc.queryCount(sql, areaPlatformId);


        //查询视频总量
        sql = "SELECT COUNT(1) count FROM video v WHERE  " +
                "EXISTS (select * from school s where v.school_id=s.id and area_platform_id = ? )";
        Integer videoTotal = areaPlatformJdbc.queryCount(sql, areaPlatformId);

        //查询评价总量
        sql = "select count(1) count from  student_evaluation_score ses left join student st on (ses.student_id=st.id) where  " +
                "EXISTS (select * from school s where st.school_id=s.id and area_platform_id = ? )";
        Integer evaluationTotal = areaPlatformJdbc.queryCount(sql, areaPlatformId);


        //查询考勤总量
        sql = "SELECT COUNT(1) count FROM class_attendlist ca WHERE  " +
                "EXISTS (select * from school s where ca.school_id=s.id and area_platform_id = ? )";
        Integer attendTotal = areaPlatformJdbc.queryCount(sql, areaPlatformId);


        Map<String, Object> map = new HashMap<>();
        //文件总量
        map.put("fileTotal", fileTotal.split(",").length);
        //通知总量
        map.put("noticeTotal", noticeTotal);
        //链接总量
        map.put("websiteTotal", websiteTotal);
        //图片总量
        map.put("picturesTotal", picturesTotal);
        //视频总量
        map.put("videoTotal", videoTotal);
        //评价总量
        map.put("evaluationTotal", evaluationTotal);
        //考勤总量
        map.put("attendTotal", attendTotal);
        return new Result(map);
    }

    @Override
    public Result findAreaLeaveAndEnter(Integer areaPlatformId) {
        logger.info("查询全区学生在离校情况");

        //查询全区学生在离校情况
        String sql = "SELECT IFNULL( SUM( CASE WHEN type = 1 OR type IS NULL THEN 1 ELSE 0 END ), 0 ) AS `leave`, " +
                        " IFNULL( SUM( CASE WHEN type = 0 THEN 1 ELSE 0 END ), 0 ) AS `enter`  " +
                        " FROM  student st LEFT JOIN import_and_export_schools iaes " +
                        " ON ( st.id = iaes.student_id AND iaes.id IN ( " +
                            " SELECT max( iaes.id )  FROM student st " +
                            " LEFT JOIN import_and_export_schools iaes ON ( st.id = iaes.student_id )  " +
                            " WHERE EXISTS ( SELECT * FROM school s WHERE st.school_id = s.id AND area_platform_id =? )" +
                            " GROUP BY st.id  HAVING max( iaes.id ) IS NOT NULL  ) " +
                "       )  WHERE  EXISTS ( SELECT * FROM school s WHERE st.school_id = s.id AND area_platform_id = ? )";
        Map<String, Object> map =  areaPlatformJdbc.queryMap(sql,areaPlatformId,areaPlatformId);

        return new Result(map);
    }

    @Override
    public Result findAreaActivityNumber(Integer areaPlatformId) {
        //查询各个学校发布的活动
        String sql = "select s.school_name , count(a.id) count,GROUP_CONCAT(a.id) id from school s left join activity a on(s.id=a.school_id) where s.area_platform_id  = ? GROUP BY s.id";
       List<Map<String,Object>> list = areaPlatformJdbc.queryListMap(sql,areaPlatformId);

        //循环每个学校发布的活动
        for (Map<String,Object> map :list) {
            //查询已报名的总和
            sql = "select  count(1) count from enrolled_students where activity_id in ("+map.get("id")+") and state = 1";
            Integer signUpTotal = areaPlatformJdbc.queryCount(sql);
            map.put("signUpTotal",signUpTotal);
            map.remove("id");
        }
        return new Result(list);
    }

    @Override
    public Result findArea30DayTemperature(Integer areaPlatformId) {
        logger.info("全区30天体温检测次数");

        String sql = "select count(1) normal , sum(if(ti_wen>37.3,1,0)) abnormal from  temperature t left join classes c on (t.class_id=c.id) where " +
                "exists (select * from school s where s.id = c.school_id and s.area_platform_id = ?) and  DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(t.monitoring_time)";

        Map<String, Object> map =  areaPlatformJdbc.queryMap(sql,areaPlatformId);
        return new Result(map);
    }

    @Override
    public Result findAreaDateTotalRanking(Integer areaPlatformId) {
        logger.info("全区数据总量排行");
        String sql = "select sum(count) count,school_id,s.school_name from (" +
                " SELECT length(GROUP_CONCAT( annex )) - length(replace(GROUP_CONCAT( annex ),',',''))+1 as count ,n.school_id " +
                " FROM notice n WHERE n.annex  IS NOT NULL AND n.annex != '' and EXISTS (select * from school s where n.school_id=s.id and area_platform_id = ?) " +
                " GROUP BY n.school_id  " +
                " UNION " +
                " SELECT COUNT(1) count,n.school_id FROM notice n WHERE EXISTS (select * from school s where n.school_id=s.id and area_platform_id = ? ) GROUP BY n.school_id " +
                " UNION " +
                " SELECT COUNT(1) count,n.school_id FROM notice n WHERE n.website  IS NOT NULL AND n.website != '' and  " +
                "EXISTS (select * from school s where n.school_id=s.id and area_platform_id = ?  ) GROUP BY n.school_id " +
                " UNION " +
                " SELECT COUNT(1) count,p.school_id FROM pictures p WHERE   EXISTS (select * from school s where p.school_id=s.id and area_platform_id = ? ) GROUP BY p.school_id " +
                " UNION " +
                " SELECT COUNT(1) count,v.school_id FROM video v WHERE  EXISTS (select * from school s where v.school_id=s.id and area_platform_id = ?) GROUP BY v.school_id " +
                " UNION " +
                " select count(1) count,st.school_id from  student_evaluation_score ses left join student st on (ses.student_id=st.id) where  " +
                "EXISTS (select * from school s where st.school_id=s.id and area_platform_id = ? )GROUP BY st.school_id " +
                " UNION " +
                " SELECT COUNT(1) count,ca.school_id FROM class_attendlist ca WHERE  EXISTS (select * from school s where ca.school_id=s.id and area_platform_id = ?) GROUP BY ca.school_id " +
                ")a left join school s on(s.id=a.school_id)  GROUP BY school_id ORDER BY sum(count) desc";

        List<Map<String,Object>> list = areaPlatformJdbc.queryListMap(sql,areaPlatformId,areaPlatformId,areaPlatformId,areaPlatformId,areaPlatformId,areaPlatformId,areaPlatformId);
        return new Result(list);
    }

    @Override
    public Result findSchoolUser(Integer areaPlatformId, Integer schoolId) {
        logger.info("查询学校用户数量-(教师，学生)");
        //教师数量
        String  sql = "select count(1) `count` from teacher where school_id = ?";
        Integer teacherCount = areaPlatformJdbc.queryCount(sql,schoolId);
        //学校数量
        sql = "select count(1) `count` from student where school_id = ?";
        Integer studentCount = areaPlatformJdbc.queryCount(sql,schoolId);

        Map<String,Object> map = new HashMap<>();
        map.put("teacherCount",teacherCount);
        map.put("studentCount",studentCount);
        return new Result(map);
    }

    @Override
    public Result findSchoolFaceRegistration(Integer areaPlatformId, Integer schoolId) {
        logger.info("查询学校人脸注册率");
        String sql = "select count(if(state = 2,1,null)) countOk ,count(if(state != 2,1,null)) countNo ,count(1) `count`," +
                "TRUNCATE(count(if(state = 2,1,null))/count(1)*100,2) percentage from student_faces_registration sfr where sfr.school_id = ?";
        Map<String,Object> map = null;
        try {
            map =  areaPlatformJdbc.queryMap(sql,schoolId);
        }catch (Exception e){
            map = new HashMap<>();
        }
        return new Result(map);
    }

    @Override
    public Result findLeavingSchool(Integer areaPlatformId, Integer schoolId) {
        logger.info("查询学校在离校数据");
        String sql = "select IFNULL(SUM(CASE WHEN type =1 or type is null THEN 1 ELSE 0 END),0) AS `leave` ," +
                " IFNULL(SUM(CASE WHEN type =0 THEN 1 ELSE 0 END),0) as `enter` from student s " +
                " LEFT JOIN import_and_export_schools iaes on(s.id=iaes.student_id and iaes.id in " +
                "(select  max(iaes.id) from student s RIGHT JOIN import_and_export_schools iaes " +
                "on(s.id=iaes.student_id) where s.school_id = ?  GROUP BY s.id ) ) where s.school_id = ? ";
        Map<String,Object> map = null;
        try {
            map =  areaPlatformJdbc.queryMap(sql,schoolId,schoolId);
        }catch (Exception e){
            map = new HashMap<>();
        }
        return new Result(map);
    }

    @Override
    public Result findParentsMessage(Integer valueOf, Integer schoolId) {
        logger.info("查询家长留言");
        //查询留言
        String sql = "select count(1) count ,TRUNCATE(sum(if(pc.state=1,1,0))/count(1)*100,2)  `read` from " +
                "parent_chat pc left join student s on (s.id=pc.student_id) where school_id = ?";

        Map<String,Object> map = null;
        try {
            map =  areaPlatformJdbc.queryMap(sql,schoolId);
        }catch (Exception e){
            map = new HashMap<>();
        }
        return new Result(map);
    }

    @Override
    public Result findDataPrecipitation(Integer valueOf, Integer schoolId) {
        logger.info("查询学校每月数据沉淀");

        Map<String,Object> map = new HashMap<>();

        //循环查询数据（0表示本月，1上月，2上上月）
        for(int i =0 ;i<3;i++ ){
            //查询学校通知，文件，连接每月发布量
            String noticesql = "select if(length(GROUP_CONCAT( annex )) - length(replace(GROUP_CONCAT( annex ),',',''))+1 is null,0,length(GROUP_CONCAT( annex )) - length(replace(GROUP_CONCAT( annex ),',',''))+1) annex ," +
                    "count(1) notice,sum(if(website is not null,1,0)) website from notice n left join class_notice cn on (n.id=cn.notice_id) where n.school_id = ? " +
                    "and PERIOD_DIFF( date_format( now() , '%Y%m' ) , date_format(opertime, '%Y%m' ) ) = ?";
            Map<String,Object> noticemap = null;
            try {
                //查询本月
                noticemap =  areaPlatformJdbc.queryMap(noticesql,schoolId,i);
            }catch (Exception e){
                noticemap = new HashMap<>();
            }

            //查询图片
            String picturessql = "select count(1) pictures from pictures p left join class_pictures cp on(p.id=cp.pictures_id) where p.school_id = ? " +
                    "and PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format(opertime, '%Y%m' ) ) = ?";
            Map<String,Object> picturesmap = null;
            try {
                //查询本月
                picturesmap =  areaPlatformJdbc.queryMap(picturessql,schoolId,i);
            }catch (Exception e){
                picturesmap = new HashMap<>();
            }

            //查询视频
            String videosql = "select count(1) video from video v left join class_video cv on(v.id=cv.video_id) where v.school_id = ? " +
                    "and PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format(opertime, '%Y%m' ) ) = ?";
            Map<String,Object> videomap = null;
            try {
                //查询本月
                videomap =  areaPlatformJdbc.queryMap(videosql,schoolId,i);
            }catch (Exception e){
                videomap = new HashMap<>();
            }

            //查询评价次数
            String  sessql = "select count(1) evaluation from student_evaluation_score ses left join student s on (s.id=ses.student_id) where s.school_id = ? " +
                    "and PERIOD_DIFF( date_format( now() , '%Y%m' ) , date_format(ses.add_time, '%Y%m' ) ) = ?";
            Map<String,Object> sesmap = null;
            try {
                //查询本月
                sesmap =  areaPlatformJdbc.queryMap(videosql,schoolId,i);
            }catch (Exception e){
                sesmap = new HashMap<>();
            }

            //查询考勤次数
            String attendsql = "select count(1) attend from class_attendlist where school_id = ? " +
                    "and  PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format(attendtime, '%Y%m' ) ) = ?";
            Map<String,Object> attendmap = null;
            try {
                //查询本月
                attendmap =  areaPlatformJdbc.queryMap(attendsql,schoolId,i);
            }catch (Exception e){
                attendmap = new HashMap<>();
            }


            Map<String,Object> month = new HashMap<>();
            month.putAll(noticemap);
            month.putAll(picturesmap);
            month.putAll(videomap);
            month.putAll(sesmap);
            month.putAll(attendmap);

            if(i==0){
                map.put("thisMonth",month);
            }else if(i==1){
                map.put("lastMonth",month);
            }else{
                map.put("lastTwoMonth",month);
            }
        }



        return new Result(map);
    }

    @Override
    public Result findShoolbancard(Integer areaPlatformId, Integer schoolId) {
        logger.info("查询学校设备的在线情况");
        //查询学校的设备在线情况
        String sql = "select sum(if(shutdown = 0,1,0))powerOn,sum(if(shutdown = 1,1,0))shutdown," +
                "(CASE WHEN `level` =0  THEN '班牌'  WHEN `level`=1  THEN '校牌'ELSE '级牌' END) bancardName  " +
                "from bancard where school_id = ? GROUP BY `level`";
        //查询班牌校牌情况
        List<Map<String,Object>> list  =  areaPlatformJdbc.queryListMap(sql,schoolId);

        return new Result(list);
    }

    @Override
    public Result findTotalMonthlyEvaluation(Integer areaPlatformId, Integer schoolId) {

        logger.info("每月评价总量");
        //查询最近六个月是那六个月
        String sql = "SELECT DATE_FORMAT(CURDATE(), '%Y-%m') AS `year_month`  " +
                "    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS `year_month`  " +
                "    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS `year_month` " +
                "    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS `year_month`  " +
                "    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS `year_month`  " +
                "    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') AS `year_month` " +
                "    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m') AS `year_month`";

        List<String> list = areaPlatformJdbc.queryListString(sql,null);
        //查询评价sql
        String scoresql = "select if( sum(if(si.type=0,1,0)) is null,0, sum(if(si.type=0,1,0))) praise,if(sum(if(si.type=1,1,0)) is null,0,sum(if(si.type=1,1,0)))improved from" +
                " student_evaluation_score ses left join score_item si on(ses.score_item_id=si.id)  WHERE si.school_id=? and   date_format(ses.add_time, '%Y-%m' ) = ?";
        //查询文字点评sql
        String textsql = "select count(1) count from text_evaluation te left join teacher t on(t.id=te.teacher_id) " +
                "where t.school_id =? and  date_format(te.add_time, '%Y-%m' ) = ? ";
        //返回数据
        Map<String,Object> returnmap = new HashMap<>();

        //循环
        for(String month:list){
            Map<String,Object> map = null;
            try {
                //查询本月
                map =  areaPlatformJdbc.queryMap(scoresql,schoolId,month);
            }catch (Exception e){
                map = new HashMap<>();
            }
            Integer  textCount = areaPlatformJdbc.queryCount(textsql,schoolId,month);
            map.put("text",textCount);
            returnmap.put(month,map);
        }

        return new Result(returnmap);
    }

    @Override
    public Result findAttendanceRanking(Integer areaPlatformId, Integer schoolId, Integer type) {

        logger.info("查询每个班的考勤详情");
        //根据学校id查询每个班的考勤详情
        String  sql ="select c.`name`,if(sum(if(attendtype = 1,1,0)) is null,0,sum(if(attendtype = 1,1,0))) total, " +
                "  if(sum(if(attendtype = 2,1,0)) is null,0,sum(if(attendtype = 1,1,0))) late, " +
                "  if(sum(if(attendtype = 5,1,0)) is null,0,sum(if(attendtype = 1,1,0))) leaveEarly, " +
                "  TRUNCATE(if(count(1)/ if(sum(if(attendtype = 8,1,0)) is null,0,sum(if(attendtype = 1,1,0)))*100  " +
                "  is null ,0.00,count(1)/ if(sum(if(attendtype = 8,1,0)) is null,0,sum(if(attendtype = 1,1,0)))*100),2)   absenceFromDuty " +
                "from classes c  left join student s on(c.id=s.class_id)  left join class_attendlist ca on(s.id=ca.userid) left join grade g on(g.id=c.grade_id ) " +
                "where c.school_id = ? and g.other = 0 GROUP BY c.id ORDER BY count(1)/ if(sum(if(attendtype = 8,1,0)) is null,0,sum(if(attendtype = 1,1,0)))*100 " +(type==0?" desc ":"asc");

        List<Map<String,Object>> list = areaPlatformJdbc.queryListMap(sql,schoolId);

        return new Result(list);
    }

    @Override
    public Result findSchoolGrade(Integer areaPlatformId, Integer schoolId) {

        logger.info("查询学校年级");
        String sql = "select id,`name` from grade where school_id = ? and other = 0";
        List<Map<String,Object>> list = areaPlatformJdbc.queryListMap(sql,schoolId);
        return new Result(list);
    }

    @Override
    public Result findGradeRankingLastWeek(Integer valueOf, Integer schoolId, Integer gradeId) {
        //查询学校总分
        String sql = "select IFNULL(sum(score),0) count from me_moral_education where school_id = ?";
        //德育项总分
        Integer score = areaPlatformJdbc.queryCount(sql,schoolId);
        //查询每个班上周的德育分数
        sql = "select c.`name` ,("+score+"-IFNULL(sum(demerit) ,0)) demerit from   classes c " +
                " left join me_moral_education_record mer on(c.id=mer.class_id and YEARWEEK(date_format(add_time,'%Y-%m-%d')) = YEARWEEK(now())-1) " +
                " left join grade g on(c.grade_id = g.id ) where c.school_id = ? and g.id=?  GROUP BY c.id ORDER BY ("+score+"-IFNULL(sum(demerit) ,0)) desc limit 5";

        List<Map<String,Object>> list = areaPlatformJdbc.queryListMap(sql,schoolId,gradeId);
        return new Result(list);
    }
}
